use crate::action::record::initialize_cache;
use rocket::{
    fairing,
    fairing::{Fairing, Info, Kind},
    Build, Rocket,
};
use rocket_db_pools::{sqlx, Database};

#[derive(Database)]
#[database("record")]
pub struct RecordDb(pub sqlx::SqlitePool);

pub struct RecordDbInitializer;

async fn create_table(db: &RecordDb, statement: &str) -> Result<(), sqlx::Error> {
    sqlx::raw_sql(statement).execute(&db.0).await.map(|_| {})
}

async fn create_main_table(db: &RecordDb) -> Result<(), sqlx::Error> {
    create_table(
        db,
        r#"
        CREATE TABLE IF NOT EXISTS main (
            id INTEGER PRIMARY KEY,
            timestamp INTEGER NOT NULL,
            type INTEGER NOT NULL
        );
        CREATE INDEX IF NOT EXISTS main_index ON main(id);"#,
    )
    .await
}

async fn create_record_type_table(db: &RecordDb) -> Result<(), sqlx::Error> {
    create_table(
        db,
        r#"
        CREATE TABLE IF NOT EXISTS record (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            timestamp INTEGER NOT NULL,
            type INTEGER NOT NULL
        );
        CREATE INDEX IF NOT EXISTS record_index ON record(id);
        CREATE TABLE IF NOT EXISTS type (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            display_name TEXT NOT NULL,
            ip_count INTEGER NOT NULL,
            port_count INTEGER NOT NULL,
            str_count INTEGER NOT NULL,
            num_count INTEGER NOT NULL
        );
        CREATE TABLE IF NOT EXISTS ip (
            id INTEGER PRIMARY KEY,
            type_id INTEGER NOT NULL,
            name TEXT NOT NULL,
            display_name TEXT NOT NULL,
            type_index INTEGER NOT NULL,
            geoip INTEGER, -- BOOLEAN
            UNIQUE(type_id, type_index),
            FOREIGN KEY(type_id) REFERENCES type(id)
        );
        CREATE TABLE IF NOT EXISTS port (
            id INTEGER PRIMARY KEY,
            type_id INTEGER NOT NULL,
            name TEXT NOT NULL,
            display_name TEXT NOT NULL,
            type_index INTEGER NOT NULL,
            UNIQUE(type_id, type_index),
            FOREIGN KEY(type_id) REFERENCES type(id)
        );
        CREATE TABLE IF NOT EXISTS str (
            id INTEGER PRIMARY KEY,
            type_id INTEGER NOT NULL,
            name TEXT NOT NULL,
            display_name TEXT NOT NULL,
            type_index INTEGER NOT NULL,
            UNIQUE(type_id, type_index),
            FOREIGN KEY(type_id) REFERENCES type(id)
        );
        CREATE TABLE IF NOT EXISTS num (
            id INTEGER PRIMARY KEY,
            type_id INTEGER NOT NULL,
            name TEXT NOT NULL,
            display_name TEXT NOT NULL,
            type_index INTEGER NOT NULL,
            UNIQUE(type_id, type_index),
            FOREIGN KEY(type_id) REFERENCES type(id)
        );"#,
    )
    .await
}

async fn create_total_table(db: &RecordDb) -> Result<(), sqlx::Error> {
    create_table(
        db,
        r#"
        CREATE TABLE IF NOT EXISTS total_yearly (
            year INTEGER PRIMARY KEY,
            count INTEGER NOT NULL
        );
        CREATE TABLE IF NOT EXISTS total_monthly (
            id INTEGER PRIMARY KEY,
            year INTEGER NOT NULL,
            month INTEGER NOT NULL,
            count INTEGER NOT NULL,
            CHECK (id = year * 12 + month),
            CHECK (month BETWEEN 1 AND 12)
        );
        CREATE TABLE IF NOT EXISTS total_weekly (
            id INTEGER PRIMARY KEY,
            year INTEGER NOT NULL,
            week INTEGER NOT NULL,
            count INTEGER NOT NULL,
            CHECK (id = year * 53 + week),
            CHECK (week BETWEEN 1 AND 53)
        );
        CREATE TABLE IF NOT EXISTS total_daily (
            id INTEGER PRIMARY KEY,
            year INTEGER NOT NULL,
            month INTEGER NOT NULL,
            day INTEGER NOT NULL,
            count INTEGER NOT NULL,
            CHECK (id = (year * 12 + month) * 31 + day),
            CHECK (day BETWEEN 1 AND 31),
            CHECK (month BETWEEN 1 AND 12)
        );"#,
    )
    .await?;
    Ok(())
}

async fn create_geoip_table(db: &RecordDb) -> Result<(), sqlx::Error> {
    create_table(
        db,
        r#"
        CREATE TABLE IF NOT EXISTS geoip_yearly (
            year INTEGER PRIMARY KEY,
            region TEXT NOT NULL,
            province TEXT,
            count INTEGER NOT NULL
        );
        CREATE TABLE IF NOT EXISTS geoip_monthly (
            id INTEGER PRIMARY KEY,
            year INTEGER NOT NULL,
            month INTEGER NOT NULL,
            region TEXT NOT NULL,
            province TEXT,
            count INTEGER NOT NULL,
            CHECK (id = year * 12 + month),
            CHECK (month BETWEEN 1 AND 12)
        );
        CREATE TABLE IF NOT EXISTS geoip_weekly (
            id INTEGER PRIMARY KEY,
            year INTEGER NOT NULL,
            week INTEGER NOT NULL,
            region TEXT NOT NULL,
            province TEXT,
            count INTEGER NOT NULL,
            CHECK (id = year * 53 + week),
            CHECK (week BETWEEN 1 AND 53)
        );
        CREATE TABLE IF NOT EXISTS geoip_daily (
            id INTEGER PRIMARY KEY,
            year INTEGER NOT NULL,
            month INTEGER NOT NULL,
            day INTEGER NOT NULL,
            region TEXT NOT NULL,
            province TEXT,
            count INTEGER NOT NULL,
            CHECK (id = (year * 12 + month) * 31 + day),
            CHECK (day BETWEEN 1 AND 31),
            CHECK (month BETWEEN 1 AND 12)
        );"#,
    )
    .await?;
    Ok(())
}

async fn set_wal_mode(db: &RecordDb) -> Result<(), sqlx::Error> {
    sqlx::query("PRAGMA journal_mode=WAL")
        .execute(&db.0)
        .await?;
    Ok(())
}

async fn initialize_record_db(db: &RecordDb) -> Result<(), sqlx::Error> {
    set_wal_mode(db).await?;
    create_main_table(db).await?;
    create_record_type_table(db).await?;
    create_total_table(db).await?;
    create_geoip_table(db).await?;

    let mut tx = db.begin().await?;
    initialize_cache(&mut tx).await?;
    tx.commit().await?;

    let result: Result<(), sqlx::Error> = Ok(());
    result
}

#[rocket::async_trait]
impl Fairing for RecordDbInitializer {
    fn info(&self) -> Info {
        Info {
            name: "Record database initializer",
            kind: Kind::Ignite,
        }
    }

    async fn on_ignite(&self, rocket: Rocket<Build>) -> fairing::Result {
        let db = RecordDb::fetch(&rocket).expect("Record database is not loaded");
        initialize_record_db(db)
            .await
            .expect("Can't initialize record database");
        fairing::Result::Ok(rocket)
    }
}
